package com.example.springboot.dao;

import com.example.springboot.entity.User;
import com.example.springboot.util.JdbcTemplateUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.HashSet;
import java.util.List;
import java.util.Set;

/**
 * Created by tang on 2018/1/5.
 */
public class UserDao {
    private JdbcTemplate jdbcTemplate;

    {

        jdbcTemplate = JdbcTemplateUtils.jdbcTemplate();
    }

    public User createUser(final User user) {

        final String sql = "insert into sys_users(username,password,salt,locked) value(?,?,?,?)";

        GeneratedKeyHolder keyHolder = new GeneratedKeyHolder();
        PreparedStatementCreator statementCreator = new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement psst = connection.prepareStatement(sql, new String[]{"id"});
                psst.setString(1, user.getUsername());
                psst.setString(2, user.getPassword());
                psst.setString(3, user.getSalt());
                psst.setBoolean(4, user.getLocked());
                return psst;
            }
        };
        jdbcTemplate.update(statementCreator, keyHolder);
        user.setId(keyHolder.getKey().longValue());
        return user;
    } //创建账户

    public void changePassword(Long userId, String newPassword) {
        String sql = "update sys_users set password=? where id=?";
        if (existsUser(userId)) {
            jdbcTemplate.update(sql, newPassword, userId);
        }

    }//修改密码

    public void correlationRoles(Long userId, Long... roleIds) {
        if (roleIds == null || roleIds.length == 0) {
            return;
        }

        String sql = "insert into sys_users_roles(user_id,role_id) value(?,?)";
        for (Long roleId : roleIds) {
            if (!exists(userId, roleId)) {
                jdbcTemplate.update(sql, userId, roleId);
            }
        }

    } //添加用户-角色关系

    public void uncorrelationRoles(Long userId, Long... roleIds) {
        if (roleIds == null || roleIds.length == 0) {
            return;
        }

        String sql = "delete from sys_users_roles where user_id=? and role_id=?";
        for (Long roleId : roleIds) {
            if (exists(userId, roleId)) {
                jdbcTemplate.update(sql, userId, roleId);
            }
        }

    }// 移除用户-角色关系

    public User findByUsername(String username) {
        String sql = "select * from sys_users where username=?";

        List<User> userList = jdbcTemplate.query(sql, new BeanPropertyRowMapper(User.class), username);
        if (userList.size() == 0) {
            return null;
        }
        return userList.get(0);
    }// 根据用户名查找用户

    public Set<String> findRoles(String username) {
        String sql="select role from sys_users u,sys_roles r,sys_users_roles ur where username=? and u.id=ur.user_id and r.id=ur.role_id";

        return new HashSet(jdbcTemplate.queryForList(sql, String.class, username));
    }// 根据用户名查找其角色

    public Set<String> findPermissions(String username) {
        String sql = "select permission from sys_users u, sys_roles r, sys_permissions p, sys_users_roles ur, sys_roles_permissions rp where u.username=? and u.id=ur.user_id and r.id=ur.role_id and r.id=rp.role_id and p.id=rp.permission_id";
        return new HashSet(jdbcTemplate.queryForList(sql, String.class, username));
    } //根据用户名查找其权限


    private boolean existsUser(Long userId) {
        String sql = "select count(1) from sys_users where id=?";
        return jdbcTemplate.queryForObject(sql, Integer.class, userId) != 0;
    }

    private boolean exists(Long userId, Long roleId) {
        String sql = "select count(1) from sys_users_roles where user_id=? and role_id=?";
        return jdbcTemplate.queryForObject(sql, Integer.class, userId, roleId) != 0;
    }
}
